package cn.share.life.module.images.utils.flowFile;

import cn.hutool.core.io.FileUtil;
import cn.hutool.core.util.NumberUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.ExcelWriter;
import org.apache.commons.lang3.StringUtils;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.io.FilenameUtils;

import java.io.File;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;

public class FlowFileMain3 {
    private static final String basePath = "E:/kuaqu2/";

    public static void main(String[] args) {
//        String specialProject = "鲁固直流";
//        String excelPath = "F:/公司3/2.2024年专项工程定价成本监审调查表-青豫直流0227.xlsx"; // Excel 文件路径
//        String fileNameMappingPath = "F:/公司3/文件目录及文号/28扎青-文件目录及文号V0.3.xlsx"; // 文件名映射路径
//        createExcelByLineDataPath(specialProject, excelPath, fileNameMappingPath);

        String specialProject2 = "盂县送出";
        String excelPath2 = "F:/公司3/0.监审表调整格式-0306-监审报表/2024年专项工程定价成本监审调查表-盂县送出0113.xlsx"; // Excel 文件路径
        String fileNameMappingPath2 = "F:/公司3/文件目录及文号/3.盂县送出-文件目录及文号V0.1.xlsx"; // 文件名映射路径
        createExcelByLineDataPath(specialProject2, excelPath2, fileNameMappingPath2);

        String specialProject3 = "天中直流";
        String excelPath3 = "F:/公司3/2024年专项工程定价成本监审调查表-哈郑直流250218.xlsx"; // Excel 文件路径
        String fileNameMappingPath3 = "F:/公司3/文件目录及文号/1.哈郑直流（天中直流）-文件目录及文号V0.3.xlsx"; // 文件名映射路径
        createExcelByLineDataPath(specialProject3, excelPath3, fileNameMappingPath3);

        String specialProject4 = "德宝直流";
        String excelPath4 = "F:/公司3/0.监审表调整格式-0306-监审报表/2.2024年专项工程定价成本监审调查表-德宝直流0227.xlsx"; // Excel 文件路径
        String fileNameMappingPath4 = "F:/公司3/文件目录及文号/7.德宝直流-文件目录及文号V0.2(1).xlsx"; // 文件名映射路径
        createExcelByLineDataPath(specialProject4, excelPath4, fileNameMappingPath4);
//
//        String specialProject5 = "中俄直流";
//        String excelPath5 = "F:/公司3/0.监审表调整格式-0306-监审报表/2.2024年专项工程定价成本监审调查表-中俄直流0218.xlsx"; // Excel 文件路径
//        String fileNameMappingPath5 = "F:/公司3/文件目录及文号/10.中俄直流-文件目录及文号V0.2.xlsx"; // 文件名映射路径
//        createExcelByLineDataPath(specialProject5, excelPath5, fileNameMappingPath5);

        String specialProject6 = "宜华直流";
        String excelPath6 = "F:/公司3/0.监审表调整格式-0306-监审报表/2024年专项工程定价成本监审调查表-宜华直流0227.xlsx"; // Excel 文件路径
        String fileNameMappingPath6 = "F:/公司3/文件目录及文号/8.宜华直流-文件目录及文号V0.2.xlsx"; // 文件名映射路径
        createExcelByLineDataPath(specialProject6, excelPath6, fileNameMappingPath6);
    }

    private static void createExcelByLineDataPath(String specialProject, String excelPath, String fileNameMappingPath) {
        // 获取 2.2024年专项工程定价成本监审调查表
        List<ProjectEntity> projectEntityList = getProjectEntityList(excelPath);

        List<List<ItemEntity>> projectItemEntityList = getDataItemEntityList(specialProject, fileNameMappingPath,  projectEntityList);
        for (List<ItemEntity> itemEntityList: projectItemEntityList) {
            if (CollectionUtils.isNotEmpty(itemEntityList)) {
                writeExcel(itemEntityList, specialProject);
            } else {
                System.out.println("没有数据可写入 Excel: " + specialProject);
            }
        }
    }

    private static List<List<ItemEntity>> getDataItemEntityList(String specialProject, String fileNameMappingPath, List<ProjectEntity> projectEntityList) {
        // 获取文件名映射
        Map<String, FileMapping> projectFileMappingMap = Maps.newConcurrentMap();
        Map<String, FileMapping> unProjectFileMappingMap = Maps.newConcurrentMap();
        List<String> numberRegexList = getFileMapping(fileNameMappingPath, projectFileMappingMap, unProjectFileMappingMap);
        // 整理内容
        List<ItemEntity> projectItemEntityList = getDataItemEntityList(specialProject, "专项工程", projectFileMappingMap, projectEntityList, numberRegexList);
        List<ItemEntity> unProjectItemEntityList = getDataItemEntityList(specialProject, "非专项工程", unProjectFileMappingMap, projectEntityList, numberRegexList);
        return Lists.newArrayList(projectItemEntityList, unProjectItemEntityList);
    }

    private static List<ItemEntity> getDataItemEntityList(String specialProject, String projectType, Map<String, FileMapping> fileMappingMap, List<ProjectEntity> projectEntityList,
                                                          List<String> numberRegexList) {

        List<ItemEntity> dataItemEntityList = Lists.newArrayList();
        Map<String, String> projectMappingMap = Maps.newConcurrentMap();
        for (Map.Entry<String, FileMapping> entry : fileMappingMap.entrySet()) {
            String key = entry.getKey();
            FileMapping fileMapping = entry.getValue();
            // 组装对象
            ItemEntity item = new ItemEntity();
            // 标准文件库类型
            String standardFileType = fileMapping.getStandardFileType();
            item.setStandardFileType(standardFileType);
            // 标准文件库类型（名字必须标准） 标准文件库类型对应的线下文件目录
            item.setStandardFileDirectory(fileMapping.getStandardFileDirectory());
            // 文件原名
            item.setFileOriginalName(FilenameUtils.getName(key));
            // 文件中的文件号
            String excelFileNumber = fileMapping.getFileNumber();
            item.setFileNumber(excelFileNumber);
            if (StringUtils.isBlank(excelFileNumber)) {
                // 文件名字中包含的，xxxx11号，有固定规则
                String fileNumber = NumberEnums.getNumber(FilenameUtils.getName(key), numberRegexList);
                if (StringUtils.isBlank(fileNumber)) {
                    fileNumber = NumberEnums.getNumber(FilenameUtils.getName(item.getStandardFileDirectory()), numberRegexList);
                    if (StringUtils.isNotBlank(fileNumber)) {
                        System.out.println("文件号未找到，使用标准文件库目录中的文件号: " + fileNumber);
                    }
                }
                item.setFileNumber(fileNumber);
            }
            // 文件信息
            setFileInfo(item, key);
            // 文件名称
            setItemFileName(item, key);
            // 专项工程名称
            setItemSpecialProject(specialProject, item, projectMappingMap);
            // 验证文件类型
            item.setDataType(ProjectTypeEnums.getType(fileMapping.getStandardFileType()));
            if (projectType.equals("非专项工程")) {
                item.setYear(getItemYearByPath(key, item.getStandardFileType()));
                item.setCustomDirectorySuggestion("");// 空
            } else {
                item.setParentProjectName("-");
                setItemProjectName(item, projectEntityList);
                // 1.若返回项目名称不为空，显示当前档案包的专项工程名称
                //2.若返回项目名称为空值，显示‘-’
                String projectName = item.getProjectName();
                if (StringUtils.isNotBlank(projectName) && !projectName.equals("-")) {
                    item.setSpecialProject(specialProject);
                } else {
                    item.setSpecialProject("-");
                }
            }
            dataItemEntityList.add(item);
        }
        return dataItemEntityList;
    }

    private static void setItemProjectName(ItemEntity item, List<ProjectEntity> projectEntityList) {
        String standardFileType = item.getStandardFileType();
        String fileNumber = item.getFileNumber();
        String baseFileName = FilenameUtils.getBaseName(item.getFileName());
        String specialProject = item.getSpecialProject();

        List<String> typeList = Lists.newArrayList("初始投资", "扩建");
        List<String> firstList = Lists.newArrayList("初始投资（含扩建）/可研储备/可行性研究报告", "初始投资（含扩建）/可研储备/可研评审意见", "初始投资（含扩建）/可研储备/可研批复报告","初始投资（含扩建）/核准备案/工程核准文件","初始投资（含扩建）/综合计划/投资计划汇总表","初始投资（含扩建）/初步设计/初步设计说明书","初始投资（含扩建）/初步设计/初设评审意见","初始投资（含扩建）/初步设计/初设批复文件","初始投资（含扩建）/投资预算/施工图预算评审意见","初始投资（含扩建）/投资预算/工程研究实验项目及费用清单","初始投资（含扩建）/竣工决算/竣工决算报告","初始投资（含扩建）/竣工决算/竣工决算审核报告", "后续投资/购置类佐证资料");
        // 后续投资/竣工决算报告
        // 后续投资中竣工决算报告，用3.2项目名去遍历文件名，如果包含，则匹配项目名称。
        if (standardFileType.equals("后续投资/竣工决算报告") || standardFileType.equals("后续投资/竣工决算审核报告")) {
            List<ProjectEntity> list = projectEntityList.stream().filter(p -> !typeList.contains(p.getType())).collect(Collectors.toList());
            boolean checkStr = checkStr(item, list, item.getFileNumber(), true);
            if (!checkStr) {
                checkStr = checkStr(item, list, baseFileName, true);
                if (!checkStr) {
                    checkStr = checkStr(item, list, baseFileName, false);
                    if (!checkStr) {
                        // 如果没有匹配的项目名称，则设置为空
                        item.setProjectName("-");
                    }
                }
            }
        } else if (standardFileType.equals("后续投资/综合计划文件")) {
            List<ProjectEntity> list = projectEntityList.stream().filter(p -> !typeList.contains(p.getType())).collect(Collectors.toList());
            if (baseFileName.contains("通知")) {
                boolean checkStr = checkStr(item, list, item.getFileNumber(), true);
                if (!checkStr) {
                    // 如果没有匹配的项目名称，则设置为空
                    item.setProjectName("-");
                }
            } else {
                list = checkStrToList(list, item.getFileNumber(), true);
                if (CollectionUtils.isEmpty(list)) {
                    // 如果没有匹配的项目名称，则设置为空
                    item.setProjectName("-");
                } else {
                    // 且3.2投运年度大于或等于当前文件包的专项工程投运年度，且文件名称中包含该专项工程的运维省公司或者文件名称中不包含所有运维省公司
//                    int year = LineAndYearEnums.getYearByLineName(specialProject);
                    List<String> areaNameListByLineName = LineAndAreaEnums.getAreaNameListByLineName(specialProject);
                    List<ProjectEntity> result = Lists.newArrayList();
                    for (ProjectEntity entity : list) {
//                        String commissioningDate = entity.getCommissioningDate();
                        String dataSource = entity.getDataSource();
//                        if (!NumberUtil.isNumber(commissioningDate)) {
//                            continue;
//                        }
//                        if (Integer.parseInt(commissioningDate) < year) {
//                            continue;
//                        }
                        if (CollectionUtils.isEmpty(areaNameListByLineName)) {
                            continue;
                        }
                        for (String areaName : areaNameListByLineName) {
                            if (dataSource.contains(areaName)) {
                                result.add(entity);
                                break;
                            }
                        }
                    }
                    if (CollectionUtils.isEmpty(result)) {
                        // 如果没有匹配的项目名称，则设置为空
                        item.setProjectName("-");
                    } else {
                        // 如果有多个项目名称，则用英文逗号拼接
                        setProjectNameOrParentName(item, result);
                    }
                }
            }
        } else if(firstList.contains(standardFileType)){
            List<ProjectEntity> list = projectEntityList.stream().filter(p -> typeList.contains(p.getType())).collect(Collectors.toList());
            boolean checkStr = checkStr(item, list, item.getFileNumber(), true);
            if (!checkStr) {
                checkStr = checkStr(item, list, baseFileName, true);
                if (!checkStr) {
                    checkStr = checkStr(item, list, baseFileName, false);
                    if (!checkStr) {
                        // 如果没有匹配的项目名称，则设置为空
                        item.setProjectName("-");
                    }
                }
            }
            // 1.若项目名称包含‘（*出资部分）’后缀，含中/英文括号字符，删除后缀
            //2.若项目名称不包含步骤1后缀，显示‘-’
            //若返回项目名称为空值，显示‘-’
            String projectName = item.getProjectName();
            if (StringUtils.isNotBlank(projectName) && !projectName.equals("-")) {
                projectName = projectName.replace("(", "（").replace(")", "）"); // 替换英文括号为中文括号
                List<String> txtList =  NumberEnums.getNumber(projectName, "\\(|\\（.*?出资部分\\）|\\）");
                if (CollectionUtils.isNotEmpty(txtList)) {
                    for (String txt : txtList) {
                        projectName = projectName.replace(txt, "");
                    }
                    List<String> projectNameList = Lists.newArrayList(projectName.split(","));
                    if (projectNameList.size() > 1) {
                        List<String> rs = projectNameList.stream().distinct().collect(Collectors.toList());
                        if (projectNameList.size() > rs.size()) {
                            item.setProjectName(String.join(",", rs));
                            item.setParentProjectName("-");
                            if (StringUtils.isNotBlank(item.getProjectYear())) {
                                // 取最大值
                                List<String> yearList = Lists.newArrayList(item.getProjectYear().split(","));
                                String year = yearList.stream().max(Comparator.comparing(Integer::valueOf)).orElse("-");
                                item.setProjectYear(year);
                            }
                            // 金额相加
                            List<String> amountList = Lists.newArrayList(item.getInvestmentAmount().split(","));
                            BigDecimal totalAmount = BigDecimal.ZERO;
                            for (String amountStr : amountList) {
                                if (NumberUtil.isNumber(amountStr)) {
                                    BigDecimal amount = new BigDecimal(amountStr);
                                    totalAmount = totalAmount.add(amount);
                                }
                            }
                            item.setInvestmentAmount(totalAmount.toString());
                        } else {
                            item.setParentProjectName(String.join(",", rs));
                        }
                    } else {
                        item.setParentProjectName(projectName);
                    }
                }
            } else {
                item.setParentProjectName("-");
            }
        } else {
            Map<String, List<ProjectEntity>> projectDataMap = projectEntityList.stream().collect(Collectors.groupingBy(ProjectEntity::getDataSource));
            Map<String, String> projectMap = getProjectInfo(item.getFileName(), projectDataMap, fileNumber);
            item.setProjectName(projectMap.getOrDefault("projectName", ""));
            item.setProjectYear(projectMap.getOrDefault("projectYear", ""));
        }
    }

    public static void setProjectNameOrParentName(ItemEntity item, List<ProjectEntity> result){
        // 如果有多个项目名称，则用英文逗号拼接
        Map<String, ProjectEntity> projectMap = result.stream().collect(Collectors.toMap(ProjectEntity::getProjectName,
                p -> p, (e1, e2) -> e1));
        List<ProjectEntity> list = Lists.newArrayList(projectMap.values());
        list.sort(Comparator.comparing(ProjectEntity::getCommissioningDate));
        String projectName = list.stream().map(ProjectEntity::getProjectName).collect(Collectors.joining(","));
        String projectYear = list.stream().map(ProjectEntity::getCommissioningDate).collect(Collectors.joining(","));
        String projectAmount = list.stream().map(ProjectEntity::getAmount).collect(Collectors.joining(","));
        item.setProjectName(projectName);
        item.setProjectYear(projectYear);
        item.setInvestmentAmount(projectAmount);
    }

    public static boolean checkStr(ItemEntity item, List<ProjectEntity> list, String str, boolean isLike) {
        if (StringUtils.isBlank(str)) {
            return false;
        }

        List<ProjectEntity> resultList = checkStrToList(list, str, isLike);
        resultList = resultList.stream().sorted(Comparator.comparing(ProjectEntity::getCommissioningDate)).collect(Collectors.toList());
        if (CollectionUtils.isNotEmpty(resultList)) {
            // 如果有多个项目名称，则用英文逗号拼接
            setProjectNameOrParentName(item, resultList);
            return true;
        } else {
            // 如果没有匹配的项目名称，则设置为空
            item.setProjectName("");
            item.setProjectYear("");
            item.setInvestmentAmount("0");
            return false;
        }
    }

    public static List<ProjectEntity> checkStrToList(List<ProjectEntity> list, String str, boolean isLike) {
        List<ProjectEntity> resultList = Lists.newArrayList();
        for (ProjectEntity projectEntity : list) {
            List<String> dataSourceList = projectEntity.getDataSourceList();
            if (CollectionUtils.isEmpty(dataSourceList)) {
                continue;
            }
            for (String dataSource : dataSourceList) {
                if (!isLike && dataSource.contains("出资部分")) {
                    dataSource = dataSource.replace("(", "（").replace(")", "）"); // 替换英文括号为中文括号
                    List<String> txtList =  NumberEnums.getNumber(dataSource, "\\(|\\（.*?出资部分\\）|\\）");
                    if (CollectionUtils.isNotEmpty(txtList)) {
                        for (String txt : txtList) {
                            dataSource = dataSource.replace(txt, "").trim(); // 去掉括号内的内容
                        }
                    }
                    if (removeSpecialCharacters2(str).contains(removeSpecialCharacters2(dataSource))) {
                        resultList.add(projectEntity);
                        break;
                    }
                }
                if (isLike && removeSpecialCharacters2(dataSource).contains(removeSpecialCharacters2(str))) {
                    resultList.add(projectEntity);
                    break;
                }
            }
        }
        return resultList;
    }

    private static String getItemYearByPath(String path, String standardFileType) {
        if (standardFileType.equals("电量电费/专项工程线路图")) {
            return "不适用";
        }
        File file = new File(path);
        String name = file.getName();
        List<String> yearByFlowNameList = getYearByName(name);
        if (CollectionUtils.isEmpty(yearByFlowNameList)) {
            File parentFile = file.getParentFile();
            if (parentFile != null) {
                return getItemYearByPath(parentFile.getPath(), standardFileType);
            }
            return "";
        }
        if (yearByFlowNameList.size() == 2 && name.contains(yearByFlowNameList.get(0) + "-" + yearByFlowNameList.get(1))) {
            // 取区间中的年份，比如 2017-2020，那么取 2017,2018,2019,2020
            int startYear = Integer.parseInt(yearByFlowNameList.get(0));
            int endYear = Integer.parseInt(yearByFlowNameList.get(1));
            List<String> years = new ArrayList<>();
            for (int year = startYear; year <= endYear; year++) {
                years.add(String.valueOf(year));
            }
            return String.join(",", years);
        } else {
            return yearByFlowNameList.get(0);
        }
    }

    private static void setItemSpecialProject(String specialProject, ItemEntity item, Map<String, String> projectMappingMap) {
        String standardFileType = item.getStandardFileType();
        List<String> unList1 = Lists.newArrayList("电量电费/专项工程线路图","资产变动/资产卡片","资产变动/资产划转文件","资产变动/资产报废文件","成本收入/历年审计报告", "电量电费/跨区专项工程清算统计表","电量电费/购售电合同","价格政策/成本监审及定价办法","价格政策/输电价格报备文件","价格政策/价格批复文件");
        List<String> unList2 = Lists.newArrayList("成本收入/公司资产负债率及带息负债融资成本率","成本收入/科目余额表","成本收入/明细账","成本收入/运维费分摊底稿","电量电费/两端换流站关口计量电量年度统计表");
        if (standardFileType.equals("电量电费/电费结算单") || standardFileType.equals("电量电费/交易结算单")) {
            // 映射关系
            if (projectMappingMap.isEmpty()) {
                String mappingPath = "F:/公司3/结算单与专项工程的映射关系V0.1.xlsx";
                projectMappingMap.putAll(getProjectMappingMap(mappingPath));
            }
            for (Map.Entry<String, String> entry : projectMappingMap.entrySet()) {
                String key = entry.getKey();
                String value = entry.getValue();
                if (item.getFileName().contains(key)) {
                    if (value.equals(specialProject)) {
                        item.setSpecialProject(value); // 设置专项工程
                        return; // 找到匹配的专项工程后退出循环
                    } else {
                        item.setSpecialProject("-"); // 设置为不适用
                        return;
                    }
                }
            }
            item.setSpecialProject("不适用");
            item.setParentProjectName("-");
        } else if (standardFileType.equals("成本收入/各类业务合同")) {
            String offlineFileDirectory = item.getOfflineFileDirectory();
            if (!offlineFileDirectory.contains("委托运维") && !offlineFileDirectory.contains("大修")) {
                item.setSpecialProject("不适用");
                return;
            }
            // 判断时间和区域
            item.setSpecialProject("-");
//            LineAndYearEnums 年份
            boolean checkSpecialProjectByYear = checkSpecialProjectByYear(specialProject, item.getFileName());
            if (!checkSpecialProjectByYear) {
                return;
            }
            // 区域判断
            checkSpecialProjectByArea(specialProject, item);
        } else if (unList1.contains(standardFileType)) {
            item.setSpecialProject(CheckFileNameMappingEnums.getValueByKey(item.getFileName(), specialProject));
        } else if (unList2.contains(standardFileType)) {
            item.setSpecialProject("不适用");
        } else {
            item.setSpecialProject(specialProject); // 设置专项工程
        }
    }

    // 判断区域
    public static void checkSpecialProjectByArea(String specialProject, ItemEntity item) {
        List<String> areaNameListByLineName = LineAndAreaEnums.getAreaNameListByLineName(specialProject);
        if (CollectionUtils.isEmpty(areaNameListByLineName)) {
            return;
        }
        for (String areaName : areaNameListByLineName) {
            if (item.getFileName().contains(areaName)) {
                item.setSpecialProject(specialProject);
                return; // 找到匹配的专项工程后退出循环
            }
        }
    }

    // 判断时间
    public static boolean checkSpecialProjectByYear(String specialProject, String fileName) {
        int year = LineAndYearEnums.getYearByLineName(specialProject);
        List<String> yearByName = getYearByName(fileName);
        if (year == 0 || CollectionUtils.isEmpty(yearByName)) {
            return false;
        }
        // 如果年份匹配，则设置专项工程
        boolean dateIsOk = false;
        for (String yearName : yearByName) {
            if (Integer.parseInt(yearName) >= year) {
                dateIsOk = true;
                break;
            }
        }
        return dateIsOk;
    }

    private static void setItemFileName(ItemEntity item, String key) {
        String name = FilenameUtils.getName(key);
        String fileName = removeStrNumberAndPoint(name).trim();
        String fileNumber = item.getFileNumber();
        String baseName = FilenameUtils.getBaseName(fileName);
        // 如果文件名的基本名称等于文件号，则直接设置文件名
        if (baseName.equals(fileNumber)) {
            item.setFileName(fileName);
            return;
        }
        fileName = fileName.replace(fileNumber, "")
                .replace("()", "")
                .replace("（）", "")
                .replace(" ", "");
        if (fileName.startsWith("-")) {
            fileName = fileName.substring(1).trim(); // 去掉开头的“-”
        }
        item.setFileName(fileName);
    }

    public static void setFileInfo(ItemEntity item, String key){
        String name = FilenameUtils.getName(key);
        File file = new File(basePath + key.replace("\\", "/"));
        String path = file.getPath();
        path = path.replace("\\", "/"); // 替换为正斜杠
        // 线下目录
        item.setOfflineFileDirectory(path.replace(basePath, "").replace("\\", "/"));
        if (file.exists()) {
            long length = file.length();
            BigDecimal lengthDecimal = BigDecimal.valueOf(length).setScale(2, RoundingMode.HALF_UP);
            BigDecimal divide = lengthDecimal.divide(BigDecimal.valueOf(1024 * 1024), 2, RoundingMode.HALF_UP);
            item.setFileSize(divide + "MB"); // 保留两位小数，单位 MB
        }
        item.setFileType(FileUtil.getSuffix(name).toLowerCase());
    }

    public static boolean dataMatching(String src, String like1, String like2) {
        if (StringUtils.isEmpty(src) || (StringUtils.isEmpty(like1) && StringUtils.isEmpty(like2))) {
            return false;
        }
        // 出去特殊字符，包含 - _ 空格 括号（中小大）
        src = removeSpecialCharacters2(src);
        return src.contains(removeSpecialCharacters2(like1)) && src.contains(removeSpecialCharacters2(like2));
    }

    // 字符串 删除 特殊字符
    private static String removeSpecialCharacters2(String str) {
       return str.replaceAll("-", "").replaceAll("_", "").replaceAll(" ", "")
               .replaceAll("（", "").replaceAll("）", "").replaceAll("\\(", "")
               .replaceAll("\\)", "").replaceAll("\\[", "").replaceAll("]", "")
               .replaceAll("\\{", "").replaceAll("}", "")
               .replaceAll("〔", "").replaceAll("〕", "")
               .replaceAll("~", "").replaceAll("—", "")
               .replaceAll("±", "").replaceAll("\\+", "")
               .replaceAll("kv", "").replaceAll("KV", "")
               .replaceAll("千伏", "").replaceAll(" ", "")
               .replaceAll("【", "").replaceAll("】", "");
    }

    private static List<String> getFileMapping(String fileNameMappingPath, Map<String, FileMapping> projectFileMappingMap,
                                                           Map<String, FileMapping> unProjectFileMappingMap) {
        List<List<Object>> projectList = ExcelUtil.getReader(new File(fileNameMappingPath), "专项工程_工程项目类").read(1);
        List<List<Object>> unProjectList = ExcelUtil.getReader(new File(fileNameMappingPath), "专项工程_非工程项目类").read(1);
        List<List<Object>> lineList = ExcelUtil.getReader(new File(fileNameMappingPath), "文号").read(1);

        // 工程类
        for (List<Object> row : projectList) {
            FileMapping fileMapping = new FileMapping();
            String path = String.valueOf(row.get(0));
            if (StringUtils.isBlank(path)) {
                continue; // 跳过空路径
            }
            fileMapping.setOfflineFileDirectory(path);
            fileMapping.setStandardFileDirectory(String.valueOf(row.get(2)));
            fileMapping.setStandardFileType(String.valueOf(row.get(1)));
            if (row.size() > 3 && null != row.get(4) && StringUtils.isNotBlank(String.valueOf(row.get(4)))) {
                fileMapping.setFileNumber(String.valueOf(row.get(4)));
            }
            projectFileMappingMap.put(fileMapping.getOfflineFileDirectory(), fileMapping);
        }
        // 非工程类
        for (List<Object> row : unProjectList) {
            FileMapping fileMapping = new FileMapping();
            String path = String.valueOf(row.get(0));
            if (StringUtils.isBlank(path)) {
                continue; // 跳过空路径
            }
            fileMapping.setOfflineFileDirectory(path);
            fileMapping.setStandardFileDirectory(String.valueOf(row.get(2)));
            fileMapping.setStandardFileType(String.valueOf(row.get(1)));
            unProjectFileMappingMap.put(fileMapping.getOfflineFileDirectory(), fileMapping);
        }

        // 文号
        List<String> numberRegexList = Lists.newArrayList();
        for (List<Object> row : lineList) {
            numberRegexList.add(String.valueOf(row.get(0)));
        }
        return numberRegexList;
    }

    private static Map<String, String> getProjectInfo(String name, Map<String, List<ProjectEntity>> projectDataMap, String number) {
        Map<String, String> result = Maps.newHashMap();
        // 去掉文件后缀
        name = FilenameUtils.getBaseName(name);
        for (Map.Entry<String, List<ProjectEntity>> entry : projectDataMap.entrySet()) {
            String key = entry.getKey();
            List<ProjectEntity> projectEntities = entry.getValue();
            projectEntities = projectEntities.stream().sorted(Comparator.comparing(ProjectEntity::getCommissioningDate)).collect(Collectors.toList());

            // 去掉中文括号和英文括号再比较
            key = key.replace("(", "（").replace(")", "）");
            number = number.replace("(", "（").replace(")", "）");
            name = name.replace("(", "（").replace(")", "）");
            if (name.endsWith("（盖章）")) {
                name = name.replace("（盖章）", ""); // 去掉“盖章”后缀
            }

            if (StringUtils.isNotBlank(number) && key.contains(number) && key.contains(name)) {
                result.put("projectName", projectEntities.stream().map(ProjectEntity::getProjectName).distinct().collect(Collectors.joining(",")));
                result.put("projectYear", projectEntities.stream().map(ProjectEntity::getCommissioningDate).distinct().collect(Collectors.joining(",")));
                return result;
            }

            if (StringUtils.isNotBlank(number) && key.contains(number)) {
                // 则从拆分出的文件名称中提取括号后缀内的‘国网??’作为关键字，若在报表3-2的项目名称中存在
                List<String> areaByFlowName = getAreaByFlowName(name);
                areaByFlowName = areaByFlowName.stream().filter(f ->f.contains("国网")).collect(Collectors.toList());
                if (CollectionUtils.isEmpty(areaByFlowName)) {
                    continue; // 如果没有地区信息，则跳过
                }
                String likeProjectName = areaByFlowName.get(0);
                likeProjectName = getText(likeProjectName);
                System.out.println("likeProjectName: " + likeProjectName);

                String finalLikeProjectName = likeProjectName;
                List<ProjectEntity> list = projectEntities.stream().filter(f -> f.getProjectName().contains(finalLikeProjectName)).collect(Collectors.toList());
                if (CollectionUtils.isEmpty(list)) {
                    System.out.println("没有匹配的项目名称: " + likeProjectName);
                    continue; // 如果没有匹配的项目名称，则跳过
                }
                list = list.stream().sorted(Comparator.comparing(ProjectEntity::getCommissioningDate)).collect(Collectors.toList());
                result.put("projectName", list.stream().map(ProjectEntity::getProjectName).collect(Collectors.joining(",")));
                result.put("projectYear", list.stream().map(ProjectEntity::getCommissioningDate).collect(Collectors.joining(",")));
                return result;
            }
        }
        return result;
    }

    private static void writeExcel(List<ItemEntity> itemEntityList, String specialProject) {

        if (CollectionUtils.isEmpty(itemEntityList)) {
            System.out.println("没有数据可写入 Excel");
            return;
        }
        String dataType = itemEntityList.get(0).getDataType();

        List<List<String>> rows = Lists.newArrayList();
        List<String> header = Lists.newArrayList("序号", "文件原名", "文件名", "文件号", "专项工程", "标准文件库类型", "标准文件库类型对应的线下文件目录", "年份", "自定义目录建议", "文件大小", "文件类型",
                "线下文件目录");
        if (dataType.equals("工程项目类")) {
            header = Lists.newArrayList("序号", "文件原名", "文件名", "文件号", "标准文件库类型", "标准文件库类型对应的线下文件目录", "文件大小", "文件类型", "专项工程",
                    "项目名称", "父项目名称", "项目投运年度", "线下文件目录", "金额");
        }
        rows.add(header);
        int index = 1;
        for (ItemEntity item : itemEntityList) {
            List<String> row = Lists.newArrayList();
            row.add(index++ + ""); // 序号
            row.add(item.getFileOriginalName());
            row.add(item.getFileName());
//            row.add(item.getDataType());
            row.add(item.getFileNumber());
            // "序号", "文件原名", "文件名", "文件号", "标准文件库类型", "标准文件库类型对应的线下文件目录", "文件大小", "文件类型", "专项工程",
            //                    "项目名称", "父项目名称", "项目投运年度", "线下文件目录"
            if (dataType.equals("工程项目类")) {

                row.add(item.getStandardFileType());
                row.add(item.getStandardFileDirectory());
                row.add(item.getFileSize());
                row.add(item.getFileType());
                row.add(item.getSpecialProject());

                row.add(item.getProjectName());
                row.add(item.getParentProjectName());
                row.add(item.getProjectYear());
                row.add(item.getOfflineFileDirectory());
                row.add(item.getInvestmentAmount());
            } else if (!dataType.equals("工程项目类")) {
                // "序号", "文件原名", "文件名", "文件号", "专项工程", "标准文件库类型", "标准文件库类型对应的线下文件目录", "年份", "自定义目录建议", "文件大小", "文件类型",
                //                "线下文件目录"
                row.add(item.getSpecialProject());
                row.add(item.getStandardFileType());
                row.add(item.getStandardFileDirectory());

                row.add(item.getYear());
                row.add(item.getCustomDirectorySuggestion());
                row.add(item.getFileSize());
                row.add(item.getFileType());
                row.add(item.getOfflineFileDirectory());
            }
            rows.add(row);
        }
        // 写入 Excel 文件
        String outputFilePath = "F:/公司3/out/" + specialProject + "_" + dataType + "_" + System.currentTimeMillis() + ".xlsx"; // 输出文件路径
        ExcelWriter writer = ExcelUtil.getWriter(outputFilePath)
                .write(rows, true);
        writer.getCellStyle().setWrapText(true); // 设置单元格文本换行
        // 设置单元格文本隐藏
//        writer.getCellStyle().setHidden(true); // 设置单元格文本隐藏
        // 设置列宽
        for (int i = 1; i < header.size(); i++) {
            if (dataType.equals("工程项目类")) {
                if (i == 7) {
                    continue;
                }
                if (i == 13 || i == 8 || i == 11 || i == 6) {
                    writer.setColumnWidth(i, 20); // 设置每列宽度为15
                    continue;
                }
                if ((i == 3 || i == 4)) {
                    writer.setColumnWidth(i, 30); // 设置每列宽度为15
                    continue;
                }
            } else {
                if (i == 7 || i == 4 || i == 9 || i == 12) {
                    writer.setColumnWidth(i, 20); // 设置每列宽度为15
                    continue;
                }
                if (i == 10) {
                    continue;
                }
                if ((i == 3 || i == 5)) {
                    writer.setColumnWidth(i, 30); // 设置每列宽度为15
                    continue;
                }
            }
            writer.setColumnWidth(i, 50); // 设置每列宽度为30
        }
        //关闭writer，释放内存
        writer.close();
    }

    // 区域判断
    public static String checkByArea(String fileName, String flowName, List<String> lineAreaList) {
        if (StringUtils.isBlank(fileName)) {
            return "";
        }
        if (CollectionUtils.isEmpty(lineAreaList)) {
            return "";
        }

        List<String> areaByFlowName = getAreaByFlowName(fileName);// 提取括号内的内容
        if (CollectionUtils.isEmpty(areaByFlowName)) {
            return flowName;
        }

        for (String area : areaByFlowName) {
            boolean flag = false;
            for (String lineArea : lineAreaList) {
                if (area.contains(lineArea)) {
                    flag = true;
                    break;
                }
            }
            if (flag) {
                return flowName; // 返回匹配的地区
            }
        }
        return "-";
    }

    // 获取文件名中的地区信息
    public static List<String> getAreaByFlowName(String name) {

        if (StringUtils.isNotBlank(name) && !name.contains("（") && !name.contains("）") && !name.contains("(") && !name.contains(")")) {
            // 如果文件名中包含括号，则提取括号内的内容
            return Lists.newArrayList();
        }
        if (StringUtils.isBlank(name)) {
            return Lists.newArrayList();
        }

        name = name.replace("(", "（").replace(")", "）"); // 替换英文括号为中文括号

        String regex = "（([^)]+)）"; // 小括号正则表达式
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(name);

        List<String> list = Lists.newArrayList();
        while (matcher.find()) {
            list.add(matcher.group(1));
        }
        return list;
    }

    public static List<String> getYearByName(String name) {
//        String regex = "20\\d{2}"; // 小括号正则表达式
        String regex = "(?<!\\d)20\\d{2}(?!\\d)";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(name);

        List<String> list = Lists.newArrayList();
        while (matcher.find()) {
            list.add(matcher.group());
        }
        return list.stream().distinct().collect(Collectors.toList());
    }

    // 项目信息表
    public static List<ProjectEntity> getProjectEntityList(String excelPath) {
        List<List<Object>> listList = ExcelUtil.getReader(new File(excelPath), "表3-2 投资明细表").read(5);

        List<ProjectEntity> projectEntities = new ArrayList<>();
        for (List<Object> row : listList) {
            ProjectEntity projectEntity = new ProjectEntity();
            projectEntity.setType(String.valueOf(row.get(1)));
            projectEntity.setDataSource(String.valueOf(row.get(2))); // 数据来源
            projectEntity.setCommissioningDate(String.valueOf(row.get(3))); // 投产日期
            projectEntity.setProjectName(String.valueOf(row.get(7))); // 项目名称

            // 投产年份
            projectEntity.setCommissioningDate(projectEntity.getCommissioningDate().split("年")[0]);

            // 金额
            if (null != row.get(26)) {
                boolean amountBln = NumberUtil.isNumber(row.get(26).toString());
                if (amountBln) {
                    projectEntity.setAmount(new BigDecimal(String.valueOf(row.get(26))).toString());
                } else {
                    projectEntity.setAmount("0");
                }
            } else {
                projectEntity.setAmount("0");
            }

            // 所有 数据源数据 17 项
            List<String> list = Lists.newArrayList(String.valueOf(row.get(2)), String.valueOf(row.get(4)),
                    String.valueOf(row.get(6)), String.valueOf(row.get(8)), String.valueOf(row.get(10)), String.valueOf(row.get(12)),
                    String.valueOf(row.get(14)), String.valueOf(row.get(16)), String.valueOf(row.get(22)), String.valueOf(row.get(25)),
                    String.valueOf(row.get(28)), String.valueOf(row.get(31)), String.valueOf(row.get(34)), String.valueOf(row.get(37)),
                    String.valueOf(row.get(40)), String.valueOf(row.get(43)), String.valueOf(row.get(45)));
            projectEntity.setDataSourceList(list);

            if (NumberUtil.isNumber(projectEntity.getCommissioningDate())) {
                projectEntities.add(projectEntity);
            }
        }
        return projectEntities;
//        return projectEntities.stream().collect(Collectors.groupingBy(ProjectEntity::getDataSource));
    }

    // 祛除序号
    public static String removeStrNumberAndPoint(String str) {
        // 使用正则表达式分割字符串
        String[] parts = str.split("/");
        // 创建一个 StringBuilder 来存储结果
        StringBuilder result = new StringBuilder();

        // 遍历每个部分，去除序号
        for (String part : parts) {
            // 去除序号
            String cleanedPart = part.replaceAll("^\\d+\\.\\d*\\s*", "");
            cleanedPart = cleanedPart.replaceAll("^\\d+、\\s*", "");

            String txt = cleanedPart.replaceAll("^\\d+-\\s*", "");
            String replace = cleanedPart.replace(txt, "").replace("-", "");
            if (!cleanedPart.equals(txt) && NumberUtil.isNumber(replace) && Integer.parseInt(replace) <= 99) {
                cleanedPart = txt;
            } else if (NumberUtil.isNumber(replace) && Integer.parseInt(replace) > 99) {
                System.out.println("未处理的部分: " + txt);
            }

            // 将处理后的部分添加到结果中
            result.append(cleanedPart).append("/");
        }
        // 去掉最后一个多余的斜杠
        if (result.length() > 0) {
            result.setLength(result.length() - 1);
        }
        return result.toString().trim();
    }

    // 祛除字符中的特殊字符
    public static String removeSpecialCharacters(String txt) {
        if (StringUtils.isBlank(txt)) {
            return txt;
        }
        if (txt.startsWith("-")) {
            txt = txt.substring(1).trim(); // 去掉开头的负号和空格
        }
        if (txt.startsWith("~$")) {
            txt = txt.substring(2).trim();
        }
        return txt.trim();
    }

    public static String getText(String text) {
        String regex = "国网(.*?)电力部分";
        Pattern pattern = Pattern.compile(regex);
        Matcher matcher = pattern.matcher(text);
        if (matcher.find()) {
            // 获取匹配的数字部分
            String day = matcher.group(1);
            // 构造结果字符串
            return "国网" + day;
        }
        return "";
    }

    private static Map<String, String> getProjectMappingMap(String mappingPath) {
        List<List<Object>> listList = ExcelUtil.getReader(new File(mappingPath)).read(1);
        Map<String, String> projectMappingMap = Maps.newHashMap();
        for (List<Object> list : listList) {
            if (CollectionUtils.isEmpty(list) || list.size() < 2) {
                continue; // 跳过空行或列数不足的行
            }
            String key = String.valueOf(list.get(0)).trim();
            String value = String.valueOf(list.get(1)).trim();
            if (StringUtils.isNotBlank(key) && StringUtils.isNotBlank(value)) {
                projectMappingMap.put(key, value);
                projectMappingMap.put(value, value);
            }
        }
        return projectMappingMap;
    }
}
